﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class SLE
    {
        public SLE()
        { }
        
        public static Guid CreateSLE(DTO.SLE sLE, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateSLE]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter AvgResponseDaysParam = new SqlParameter("@AvgResponseDays", SqlDbType.TinyInt);
                    SqlParameter AvgResponseTimeMSParam = new SqlParameter("@AvgResponseTimeMS", SqlDbType.Int);
                    SqlParameter MaxResponseDaysParam = new SqlParameter("@MaxResponseDays", SqlDbType.TinyInt);
                    SqlParameter MaxResponseTimeMSParam = new SqlParameter("@MaxResponseTimeMS", SqlDbType.Int);
                    SqlParameter AvailabilityParam = new SqlParameter("@Availability", SqlDbType.Decimal);
                    SqlParameter ConcurrentUsersParam = new SqlParameter("@ConcurrentUsers", SqlDbType.Int);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = sLE.Name;

                    if (sLE.Description != null)
                    {
                        DescriptionParam.Value = sLE.Description;
                    }
                    else
                    {
                        DescriptionParam.Value = DBNull.Value;
                    }

                    if (sLE.AvgResponseDays != 0)
                    {
                        AvgResponseDaysParam.Value = sLE.AvgResponseDays;
                    }
                    else
                    {
                        AvgResponseDaysParam.Value = DBNull.Value;
                    }

                    if (sLE.AvgResponseTimeMS != 0)
                    {
                        AvgResponseTimeMSParam.Value = sLE.AvgResponseTimeMS;
                    }
                    else
                    {
                        AvgResponseTimeMSParam.Value = DBNull.Value;
                    }

                    if (sLE.MaxResponseDays != 0)
                    {
                        MaxResponseDaysParam.Value = sLE.MaxResponseDays;
                    }
                    else
                    {
                        MaxResponseDaysParam.Value = DBNull.Value;
                    }

                    if (sLE.MaxResponseTimeMS != 0)
                    {
                        MaxResponseTimeMSParam.Value = sLE.MaxResponseTimeMS;
                    }
                    else
                    {
                        MaxResponseTimeMSParam.Value = DBNull.Value;
                    }

                    if (sLE.Availability != 0)
                    {
                        AvailabilityParam.Value = sLE.Availability;
                    }
                    else
                    {
                        AvailabilityParam.Value = DBNull.Value;
                    }

                    if (sLE.ConcurrentUsers != 0)
                    {
                        ConcurrentUsersParam.Value = sLE.ConcurrentUsers;
                    }
                    else
                    {
                        ConcurrentUsersParam.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(AvgResponseDaysParam);
                    cmd.Parameters.Add(AvgResponseTimeMSParam);
                    cmd.Parameters.Add(MaxResponseDaysParam);
                    cmd.Parameters.Add(MaxResponseTimeMSParam);
                    cmd.Parameters.Add(AvailabilityParam);
                    cmd.Parameters.Add(ConcurrentUsersParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

        public static DTO.SLE UpdateSLE(DTO.SLE sLE, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateSLE]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter AvgResponseDaysParam = new SqlParameter("@AvgResponseDays", SqlDbType.TinyInt);
                    SqlParameter AvgResponseTimeMSParam = new SqlParameter("@AvgResponseTimeMS", SqlDbType.Money);
                    SqlParameter MaxResponseDaysParam = new SqlParameter("@MaxResponseDays", SqlDbType.TinyInt);
                    SqlParameter MaxResponseTimeMSParam = new SqlParameter("@MaxResponseTimeMS", SqlDbType.Money);
                    SqlParameter AvailabilityParam = new SqlParameter("@Availability", SqlDbType.Decimal);
                    SqlParameter ConcurrentUsersParam = new SqlParameter("@ConcurrentUsers", SqlDbType.Int);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = sLE.ID;
                    NameParam.Value = sLE.Name;

                    if (sLE.Description != null)
                    {
                        DescriptionParam.Value = sLE.Description;
                    }
                    else
                    {
                        DescriptionParam.Value = DBNull.Value;
                    }
                    
                    AvgResponseDaysParam.Value = sLE.AvgResponseDays;                    
                    AvgResponseTimeMSParam.Value = sLE.AvgResponseTimeMS;
                    MaxResponseDaysParam.Value = sLE.MaxResponseDays;
                    MaxResponseTimeMSParam.Value = sLE.MaxResponseTimeMS;
                    AvailabilityParam.Value = sLE.Availability;
                    ConcurrentUsersParam.Value = sLE.ConcurrentUsers;
                   
                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(AvgResponseDaysParam);
                    cmd.Parameters.Add(MaxResponseDaysParam);
                    cmd.Parameters.Add(AvgResponseTimeMSParam);
                    cmd.Parameters.Add(MaxResponseTimeMSParam);
                    cmd.Parameters.Add(ConcurrentUsersParam);
                    cmd.Parameters.Add(AvailabilityParam);

                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return sLE;
        }

        public static void DeleteSLE(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteSLE]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }

        public static DTO.SLE GetSLE(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.SLE sLE = new DTO.SLE();
            const string sprocname = "[ServiceEngine].[GetSLE]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            sLE.ID = reader.GetGuid(0);
                            sLE.Name = reader.GetString(1);

                            if (reader.IsDBNull(2))
                            {
                                sLE.AvgResponseDays = 0;
                            }
                            else
                            {
                                sLE.AvgResponseDays = reader.GetByte(2);
                            }
                            if (reader.IsDBNull(3))
                            {
                                sLE.AvgResponseTimeMS = 0;
                            }
                            else
                            {
                                sLE.AvgResponseTimeMS = reader.GetInt32(3);
                            }
                            if (reader.IsDBNull(4))
                            {
                                sLE.MaxResponseDays = 0;
                            }
                            else
                            {
                                sLE.MaxResponseDays = reader.GetByte(4);
                            }
                            if (reader.IsDBNull(5))
                            {
                                sLE.MaxResponseTimeMS = 0;
                            }
                            else
                            {
                                sLE.MaxResponseTimeMS = reader.GetInt32(5);
                            }
                            if (reader.IsDBNull(6))
                            {
                                sLE.Availability = 0;
                            }
                            else
                            {
                                sLE.Availability = reader.GetDecimal(6);
                            }
                            if (reader.IsDBNull(7))
                            {
                                sLE.ConcurrentUsers = 0;
                            }
                            else
                            {
                                sLE.ConcurrentUsers = reader.GetInt32(7);
                            }

                            if (reader.IsDBNull(8))
                            {
                                sLE.Description = null;
                            }
                            else
                            {
                                sLE.Description = reader.GetString(8);
                            }

                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return sLE;

        }

        public static DTO.SLE GetSLEForActorCapability(Guid actorID, Guid capabilityID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.SLE sLE = new DTO.SLE();
            const string sprocname = "[ServiceEngine].[GetSLEForActorCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter ActorIDParam = new SqlParameter("@ActorID", SqlDbType.UniqueIdentifier);
                    SqlParameter CapabilityIDParam = new SqlParameter("@CapabilityID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    ActorIDParam.Value = actorID;
                    CapabilityIDParam.Value = capabilityID;

                    cmd.Parameters.Add(ActorIDParam);
                    cmd.Parameters.Add(CapabilityIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            sLE.ID = reader.GetGuid(0);
                            sLE.Name = reader.GetString(1);

                            if (reader.IsDBNull(2))
                            {
                                sLE.AvgResponseDays = 0;
                            }
                            else
                            {
                                sLE.AvgResponseDays = reader.GetByte(2);
                            }
                            if (reader.IsDBNull(3))
                            {
                                sLE.AvgResponseTimeMS = 0;
                            }
                            else
                            {
                                sLE.AvgResponseTimeMS = reader.GetInt32(3);
                            }
                            if (reader.IsDBNull(4))
                            {
                                sLE.MaxResponseDays = 0;
                            }
                            else
                            {
                                sLE.MaxResponseDays = reader.GetByte(4);
                            }
                            if (reader.IsDBNull(5))
                            {
                                sLE.MaxResponseTimeMS = 0;
                            }
                            else
                            {
                                sLE.MaxResponseTimeMS = reader.GetInt32(5);
                            }
                            if (reader.IsDBNull(6))
                            {
                                sLE.Availability = 0;
                            }
                            else
                            {
                                sLE.Availability = reader.GetDecimal(6);
                            }
                            if (reader.IsDBNull(7))
                            {
                                sLE.ConcurrentUsers = 0;
                            }
                            else
                            {
                                sLE.ConcurrentUsers = reader.GetInt32(7);
                            }

                            if (reader.IsDBNull(8))
                            {
                                sLE.Description = null;
                            }
                            else
                            {
                                sLE.Description = reader.GetString(8);
                            }

                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return sLE;

        }

        public static DTO.SLEList ListSLEs(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.SLEList list = new DTO.SLEList();
            const string sprocname = "[ServiceEngine].[ListSLEs]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.SLEs = new List<DTO.SLEListElement>();
                            while (reader.Read())
                            {
                                DTO.SLEListElement element = new DTO.SLEListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.SLEs.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }
        
        public static List<DTO.SLEListElement> ListSLEsByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.SLEListElement> list = new List<DTO.SLEListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.SLEListElement>();
                            while (reader.Read())
                            {
                                DTO.SLEListElement element = new DTO.SLEListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }
        
    }
}
